#ifndef _DbHandler_h_
#define _DbHandler_h_

#include <PostgreSQL/PostgreSQL.h> // /usr/local/pgsql/include must be search path, /opt/PostgresPlus/8.4SS, 
// /media/green1000/Data/data.  pwd=postgres/postgres, Port=5432, v=8.4.1.2, autoupdate installed.
#define SCHEMADIALECT <PostgreSQL/PostgreSQLSchema.h>
#define MODEL <jammit/jammit.sch> // My statement description of the jammit database (not specific to any implementation, sqlite or MySQL)
#include "Sql/sch_header.h" // U++ tools and macros (hairy) to enable compile-time SQL syntax checking and the ability to reference columns by constants instead of position, which greatly reduces risk of error.  Value objects also support automatic conversion from NULL to SQL NULL.
#include <Sql/sch_schema.h>
#include <Sql/sch_source.h>
#include <unistd.h> // For gethostname

typedef PostgreSQLSession DbSession; // Create an alias to increase abstraction

class DbHandler {
	
public:
	// Database objects
	int userID;
	String userName, usersTableName;
	String machineName, machinesTableName;
	String networkName, networksTableName;
	String songsTableName;
	int listID; // Active list id so that the handlers know what to save.
	String listsTableName, listName;
	
	String userTempDir;
	String appSharedDir;
	String userDir;
	String userLocalDataDir;
	String userRoamingDataDir;
	String commonFiles;
	// TODO: Convert to a collection of reusable connections so don't waste time reconnecting
	DbSession db; // db for the main grid on the right, playlistdb for the grid on the right when it needs to update.  We may be able to get away with a single update connection, though.
	DbSession playlistdb; // 

	//============================================================================================================================
	DbHandler() {
		Connect(db); // This db ends up being used by the grid on the right (the main grid) as a select for the cursor.  Any cursor seems to need its own connection to maintain state.
		Connect(playlistdb);
	}
	
	//============================================================================================================================
	~DbHandler() {

		// Critical or will get bad memory error
		db.Close();
		playlistdb.Close();
	}
	
	// Dead on Arrival.  Kill oneself.
	//============================================================================================================================
	void DOA(const char *msg, int eno, Sql& lst) {
		// Kill ourselves and rollback
		
		LOG(msg << lst << "," << DeQtf(lst.GetLastError()));
		Exclamation(Format("%s: %s: %s", msg, lst.ToString(), DeQtf(lst.GetLastError())));
		lst.Rollback();
		// If a negative errno is sent, then don't exit.  Such as connection failures, perhaps its a not necessary connection to be able to continue to work.
		_exit(eno);
	}

	//============================================================================================================================
	void DOA(const char *msg, int eno) {
		// Kill ourselves and rollback
		
		LOG(msg);
		Exclamation(Format("%s", msg));
		// If a negative errno is sent, then don't exit.  Such as connection failures, perhaps its a not necessary connection to be able to continue to work.
		_exit(eno);
	}

	//============================================================================================================================
	void Initialize(DbSession &db) {
		// Database operations for the main grid(can be skipped if in direct file mode)	
		// I think I set up PostgreSQL to have a maximum of 15 connections.  Theoretically you could kick off a bunch of update threads, with 5 or 6 lists open on the main page.
		// We need to convert this to be a shared main thread update, since the user can't update simultaneously both grids.
		// The SavePlayList will grab a Sql object off this db, assuming its open.
//		dbHandler.Connect(playlistdb);  
		Sql lst(db);
		Sql update(db);  // Note that this is not used within a cursor

		// TODO: Make a text cache or binary cache (ooh!) and check last change dir
		// TODO: Can we reference Windows indexing?
		
		// These are linux vars
		userName = GetEnv("USERNAME"); // TODO: Verify a value (or USER)
		usersTableName = "users_001";
		char cmachinename[HOST_NAME_MAX + 1];
		gethostname((char *)cmachinename, sizeof cmachinename); // SUSv2 guarantees that 'Host names are limited to 255 bytes'. POSIX.1-2001 guarantees that 'Host names (not including the terminating null byte) are limited to HOST_NAME_MAX bytes'.
		machineName = cmachinename;
		// HOSTTYPE=x86_64, MACHTYPE=x86_64-pc-linux-gnu
		// PWD is current directory
		machinesTableName = "machines_001";
		networkName = "home"; // Don't have code to detect this yet
		networksTableName = "networks_001";
		listsTableName = "lists_001";
		songsTableName = "songs_001";
		
		userTempDir = GetEnv("TEMP");
		appSharedDir = GetEnv("ProgramData"); // or ALLUSERSPROFILE or 
		userDir = GetEnv("HOMEPATH"); // or USERPROFILE
		userLocalDataDir = GetEnv("LOCALAPPDATA"); // Should we use this instead of Users/Jeff?
		userRoamingDataDir = GetEnv("APPDATA");
		commonFiles = GetEnv("CommonProgramFiles");
		int rowct = 0;
		
		// Check to see if user definition table exists in this database using standard metadata tables.

		if (!(lst * 
			Select(SqlCountRows())
			.From(SqlId("information_schema.tables"))
			.Where(SqlId("table_name") == usersTableName))) // Unlike Oracle, MySql stores the names as they are cased.
		{
			// Metadata information_schema not even there!
			DOA("Compilation failure of query against information_schema", 1, lst);
		} else {
			
			// Query compiled; metadata informat_schema there
			
			LOG(lst);				
			if (!lst.Fetch()) {
				DOA("Fetch failure from query against information_schema", 2, lst);
			}
			
			// If the query ran but there is no record of the user_tbl, then we need to creat it
			if (lst[0] == 0) {
				// User table required to do anything, or I guess we could create it
				// TODO: Need a baseline sample to dup from		
				update.Execute(CAT << "DROP TABLE " << usersTableName);
				update.ClearError();
				if (!(update.Execute(CAT << "CREATE TABLE " << usersTableName 
					<<	"(userid INTEGER, "
					<<	"username VARCHAR(255), "
					<<	"users_tbl VARCHAR(255) DEFAULT '" << usersTableName << "', "
					<<	"defaultsongs_tbl VARCHAR(255) DEFAULT 'songs_001', "
					<<	"rawsongs_tbl VARCHAR(255) DEFAULT 'songs_001', "
					<<	"keepersongs_tbl VARCHAR(255) DEFAULT 'songs_001', "
					<<	"removesongs_tbl VARCHAR(255) DEFAULT 'songs_removed', "
					<<	"defaultmachines_tbl VARCHAR(255) DEFAULT 'machines_001', "
					<<	"defaultlists_tbl VARCHAR(255) DEFAULT 'lists_001', "
					<<  "defaultlistid INTEGER DEFAULT 1, "
					<<	"defaultmachinename VARCHAR(255) DEFAULT '" << machineName << "', "
					<<	"useraliases_coll VARCHAR(255), "
					<<	"comments TEXT, "
					<<	"CONSTRAINT pk_" << usersTableName << "_1 PRIMARY KEY (userid, users_tbl))"
					<<  " COMMENT 'Created on the fly by jammit';")))
				{
					DOA("Execution failure creating user table, possibly syntax: ", 3, update);
				}
			}
			//ASSERT_(rowct, CAT << "In db " << databasePath << ", user table missing: " << localUserTableName);
		}

		// TODO: If table missing, look for similary; determine primary user table, db folder, db name from config file
		
		// Table user_tbl is present; check it for our current user
		if (!(lst * 
			Select(SqlCountRows().As(COUNT)) // RawSongs table actually indicates keepers, but a separate keepers table should be slimmer than the raw table, so better performing
			.From(USERS_001)
			.Where(USERS_TBL == usersTableName 
				&& USERNAME == userName)) ) // Name:jeff on Ubuntu system
		{
			DOA("Compilation failure of SQL fetching from users table for current user name", 4, lst);
		} else {
			
			// Query valid; compiled
			LOG(lst);
			
			// Attempt to fetch first row
			if (!lst.Fetch()) {
				DOA("Fetch failure of SQL fetching from users table for current user name", 5, lst);
			}
			int nextID = 0;
			
			// If the current user is not present for that user table name, table may be null, or configured on a different name.  NOCASE collation should prevent case issues.				
			if (lst[COUNT] == 0) {
				LOG("No data in user table: " << usersTableName << " for user: " << userName << " sql: " << lst);
				// Get a sequence id for the user id of the user we will create
				lst.Begin();  // Begin atomic transaction: Do not alter next sequence until user successfully created and read back
				if (!(lst *
				    Select(NEXTID)
				    .From(SEQUENCES_001)
				    .Where(TABLENAME == usersTableName)))
				{
					DOA("Compilation failure of SQL to fetch next sequence for user table", 6, lst);
				} else {
					
					// Fetch for sequence compiled, now attempt to fetch
					if (lst.Fetch()) {
				        nextID = lst[NEXTID];
				        LOG("A key was available");
				    } 

					// TODO: Validate id free.  Consider switching to mysql sequences if available.					
					// Write back the next value.  If row exists, its set for replace.  We trust it to not collide, but that's not necessarily safe				   
					if (!(lst.Execute(CAT << "INSERT INTO sequences_001(tablename, nextid) VALUES('" << usersTableName << "', " << nextID + 1 << ")")))
					{
						// Error compiling update of key, or duplicate and replace option not set on table.  Check error code.
						// Check for duplicate key.
						
						DOA("Execution failure of SQL to update sequence for user table", 7, lst);
					} else {
						// Executed; assume one row created
						LOG(lst);
					}
				}
				
				// Create a user
				
				if (!(lst * Insert(USERS_001)
					(USERID, nextID)
					(USERS_TBL, usersTableName)
					(USERNAME, userName)
					(DEFAULTLISTS_TBL, "lists_001")
					(DEFAULTLISTID, 1)
					))
				{
					DOA("Compilation failure of SQL to add new user to user table", 8, lst);
				}
			}
		}

		// Fetch user detail now that either one already existed or we created one or we created the whole damn table! Hell, maybe we created the whole damn database!
		
		if (!(lst * 
			Select(USERID, USERS_TBL, USERNAME, DEFAULTSONGS_TBL, DEFAULTLISTS_TBL, DEFAULTLISTID) // RawSongs table actually indicates keepers, but a separate keepers table should be slimmer than the raw table, so better performing
			.From(USERS_001)
			.Where(USERS_TBL == usersTableName 
				&& USERNAME == userName)))
		{
			DOA("Compilation failure of SQL to read new user we just created in user table", 9, lst);
		} else {
			
			if (!lst.Fetch()) {
				DOA("Internal error: User we just created not available in database", 10, lst);
			}

			// Got to checkpoint; save user			
			lst.Commit();
			userID = lst[USERID];
			usersTableName = lst[USERS_TBL];
			userName = lst[USERNAME];
			songsTableName = lst[DEFAULTSONGS_TBL];
			listsTableName = lst[DEFAULTLISTS_TBL];

			// If user not set up, we have to just take a gander (log)
			if (listsTableName.IsEmpty()) {
				listsTableName = "lists_001";
				LOG("Guessing a default list table" << listsTableName);
			}
			
			if (lst[DEFAULTLISTID].IsNull()) {
				listID = 1;
				LOG("Guessing a default list id" << listID);
			} else {
				listID = lst[DEFAULTLISTID];
			}
			
			// Verify list exists
			if (!(lst * 
				Select(LISTID) // RawSongs table actually indicates keepers, but a separate keepers table should be slimmer than the raw table, so better performing
				.From(LISTS_001)
				.Where(LISTID == listID
				&& LISTS_TBL == "lists_001"
				)
				))
			{
				DOA("Compilation failure of SQL to fetch play list", 11, lst);
			} else {
				int rowcount = lst.GetRowsProcessed();
				if (rowcount == 0) {
					// Create a list
					listName = CAT << "Default Play List for " << userName;
					
					if (!(lst * Insert(LISTS_001)
						(LISTID, listID)
						(LISTS_TBL, "lists_001")
						(LISTNAME, listName)
						(USERNAME, userName)
						(USERS_TBL, usersTableName)
						))
					{
						DOA("Compilation failure of SQL to create default play list", 12, lst);
					}
				} else if (rowcount > 1) {
					DOA("Architectural failure of output showing more than one default list with same ID", 13, lst);
				}
			}
			
			// Fetch list detail
			if (!(lst * 
				Select(LISTID, LISTS_TBL, LISTNAME, COMMENTS) // RawSongs table actually indicates keepers, but a separate keepers table should be slimmer than the raw table, so better performing
				.From(LISTS_001)
				.Where(LISTID == listID 
				&& LISTS_TBL == listsTableName
				)
				))
			{
				DOA("Compilation failure of SQL to read list detail", 13, lst);
			}			
			
			if (!lst.GetRowsProcessed()) {
				DOA("Internal failure of SQL to read at least one row from list detail", 14, lst);
			}
			
			lst.Fetch();
			listID = lst[LISTID];
			listsTableName = lst[LISTS_TBL];
			listName = lst[LISTNAME];
		}

		if (listID == 0) {
			DOA("Active List ID cannot be zero", 15);
		}
		
		if (listsTableName.IsEmpty()) {
			DOA("active Lists table name is empty.  It appears to not be populated in lists_001.  Please verify", 16);
		}
	}

	//============================================================================================================================
	DbSession &getPlaylistDb() {
		return playlistdb;
	}
	
	//============================================================================================================================
	void Connect(DbSession &db) {
		// Case sensitive pwd
		if(!db.Open(("host=localhost dbname=postgres user=postgres password=postgres"))) {
			Exclamation(Format("Error in open: %s", DeQtf(db.GetLastError())));
			return;
		}
	
		Sql temp(db);
		if (!(temp.Execute("set search_path to mydb"))) {
			Exclamation(Format("Error in setting searchpath to mydb schema: %s", DeQtf(temp.GetLastError())));
			return;
		}
		
		if (!(temp.Execute("set default_text_search_config = 'pg_catalog.english'"))) {
			Exclamation(Format("Error in setting text search to english: %s", DeQtf(temp.GetLastError())));
			return;
		}

		// On the current linux, 10.04, default is UTF-8, and U++ is KOI8R, so special 2-byte characters get hosed when writing to the database, which is UTF8.
/*
		if (!(temp.Execute("set client_encoding to 'KOI8R'"))) {
			Exclamation(Format("Error in setting client encoding in KOI8R: %s", DeQtf(temp.GetLastError())));
			return;
		}
*/
	}
};

#endif
